set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 5000;
set hive.exec.max.dynamic.partitions = 30000;
with center_arrival_base as (--取t-2扫描时间 中心到件
    select waybill_no                  --运单编号
         , package_code                --所属包号
         , scan_site_code              --扫描网点code
         , scan_site as scan_site_name --扫描网点
         , network_type                --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
         , scan_time                   --路段目的站点最晚发件时间
         , source                      --来源 PC端,自动分拣设备,移动端
         , site_agent_code
         , site_agent_name
         , weight
    from (
             select waybill_no     --运单编号
                  , package_code   --所属包号
                  , scan_site_code --扫描网点code
                  , scan_site      --扫描网点
                  , network_type   --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
                  , scan_time      --路段目的站点最晚发件时间
                  , source         --来源 PC端,自动分拣设备,移动端
                  , site_agent_code
                  , site_agent_name
                  , weight
                  , row_number() over (partition by waybill_no,scan_site_code order by scan_time desc) as rn
             from jms_dwd.dwd_tab_barscan_centerarrival_base_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -1) -- 数据为T-2的数据
               and dt <= '{{ execution_date | cst_ds }}'
               and waybill_no like 'JT%'
               and waybill_no is not null
               and waybill_no <> ''
               and network_type = 2                 --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
               and date(scan_time) = date_add('{{ execution_date | cst_ds }}', -1)
         ) a
    where rn = 1
),
     center_send_base as (--取t-2扫描时间  中心发件 获取下一站
         select waybill_no --运单编号
              , scan_site_code
              , max(next_type_flag) as next_type_flag
         from (
                  select waybill_no                                              --运单编号
                       , scan_site_code
                       , if(next_network_type not in (3, 4), 0, 1) as next_type_flag --1代表下一站是网点或集散
                  from jms_dwd.dwd_tab_barscan_centersend_base_dt
                  where dt >= date_add('{{ execution_date | cst_ds }}', -1)
                    and dt <= '{{ execution_date | cst_ds }}'
                    and date(scan_time) = date_add('{{ execution_date | cst_ds }}', -1)
                    and waybill_no like 'JT%'
                    and network_type = 2 --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
              ) a
         group by waybill_no, scan_site_code
     ),
     result_table as (
         select t1.waybill_no                                       --运单编号
              , t1.package_code                                     --所属包号
              , t1.scan_site_code                                   --扫描网点code
              , t1.scan_site_name                                   --扫描网点
              , t1.network_type                                     --扫描网点类型，1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他
              , t1.scan_time                                        --路段目的站点最晚发件时间
              , t1.source                                           --来源 PC端,自动分拣设备,移动端
              , t1.site_agent_code                                  --代理区code
              , t1.site_agent_name                                  --代理区name
              , t1.weight                                           --到件重量
              , t2.next_type_flag                                   --1代表下一站是网点或集散 0其他
              , if(t2.waybill_no is not null, 1, 0) as is_send_flag --1做了到件 0 没做到件
         from center_arrival_base t1
                  left join center_send_base t2
                            on t1.waybill_no = t2.waybill_no
                                and t1.scan_site_code = t2.scan_site_code
     )
insert
overwrite
table
jms_dm.dm_tab_inport_center_summary_dt
partition
(
dt
)
select scan_site_code             as scan_site_code  --扫描网点code
     , scan_site_name             as scan_site_name  --扫描网点
     , site_agent_code            as site_agent_code --代理区code
     , site_agent_name            as site_agent_name --代理区name
     , count(1)                   as arr_num         --到件总量
     , sum(if(weight > 0, 1, 0))  as weight_num      --称重票数
     , sum(if(weight > 0 and source = 'PC端', 1,
              0))                 as weight_pc_num   --PC端称重票数
     , sum(if(weight > 0 and source = '移动端', 1,
              0))                 as weight_yd_num   --内场APP称重票数
     , sum(if(weight > 0 and source not in ('移动端', 'PC端'), 1,
              0))                 as weight_jcd_num  --交叉带称重票数
     , date_add('{{ execution_date | cst_ds }}', -1) as date_time       --日期
     , date_add('{{ execution_date | cst_ds }}', -1) as dt              --日期
from result_table t1
where next_type_flag = 1
  and is_send_flag = 1
group by scan_site_code
       , scan_site_name
       , site_agent_code
       , site_agent_name
    distribute by dt
;
